package com.aliyun.heiyu.powermaster.server.rotary.contants;

/**
 * @Description 服务器sql
 * @Author shun.zhang
 * @Date 2019/12/31 10:39
 **/
public interface ServerSqlContant {
    /**
     * 添加服务器数据
     */
    String SAVE_SERVERDATA = "insert d_state_grid_collect_info(gmt_create,collect_time,server_sn,inlet_temp) VALUES(?,?,?,?)";
    /**
     * 查询服务器
     */
    String SELECT_SERVERLIST = "select device_id from d_device_common where type = 'server'";

    /**
     * 制冷模块查询湖州服务器的功率cpu使用率
     */
    String SELECT_COOLDATA = "select server_sn,server_power,cpu_usage,collect_time from state_grid_collect_info where server_sn in ";

    /**
     * 查询机柜下的服务器最高温度的服务器
     */
    String SELECT_CAB_SERVER = "select ddc.located_cabinet located_cabinet,max(co.inlet_temp) inlet_temp,SUBSTRING_INDEX(GROUP_CONCAT(co.server_sn ORDER BY co.inlet_temp desc),',',1) server_sn " +
        " FROM (select coll.inlet_temp inlet_temp,coll.server_sn server_sn " +
        "from (select MAX(collect_time) collect_time,server_sn from state_grid_collect_info group by server_sn) dd " +
        "left join state_grid_collect_info coll on dd.collect_time = coll.collect_time and dd.server_sn = coll.server_sn" +
        ") co" +
        " left join device_common ddc on ddc.device_id = co.server_sn" +
        " group by ddc.located_cabinet";

    /**
     * 根据机柜主键查询机柜下服务器的功率和温度
     */
    String SELECT_CABINET_SERVER_TEMP = "select ddc.located_cabinet located_cabinet,co.inlet_temp inlet_temp,SUBSTRING_INDEX(GROUP_CONCAT(co.server_sn ORDER BY co.inlet_temp desc),',',1) server_sn " +
        " FROM (select coll.inlet_temp inlet_temp,coll.server_sn server_sn " +
        "from (select MAX(collect_time) collect_time,server_sn from d_state_grid_collect_info group by server_sn) dd " +
        "left join d_state_grid_collect_info coll on dd.collect_time = coll.collect_time and dd.server_sn = coll.server_sn" +
        ") co" +
        " left join d_device_common ddc on ddc.device_id = co.server_sn where ddc.located_cabinet = ?" +
        " group by ddc.located_cabinet having ddc.located_cabinet=?";


        //"SELECT ci.server_power,ci.inlet_temp,ci.collect_time,ci.server_sn,c.located_cabinet " +
        //"FROM d_state_grid_collect_info ci INNER JOIN d_device_common c " +
        //"ON ci.server_sn = c.device_id WHERE c.located_cabinet = ?  GROUP BY ci.server_sn";

    /**
     * 推荐温度
     */
    String RECOMMENDCABINET = "select ddc.located_cabinet located_cabinet,max(co.inlet_temp) inlet_temp,SUBSTRING_INDEX(GROUP_CONCAT(co.server_sn ORDER BY co.inlet_temp desc),',',1) server_sn " +
        " FROM (select coll.inlet_temp inlet_temp,coll.server_sn server_sn " +
        "from (select MAX(collect_time) collect_time,server_sn from state_grid_collect_info group by server_sn) dd " +
        "left join state_grid_collect_info coll on dd.collect_time = coll.collect_time and dd.server_sn = coll.server_sn" +
        ") co" +
        " left join device_common ddc on ddc.device_id = co.server_sn" +
        " WHERE ddc.`located_cabinet` = 'AM225_01_CB'"+
        " group by ddc.located_cabinet";

    /**
     * 通过机柜主键获取该机柜下服务器的主键
     */
    String SERVER_IDS_BY_CABINET = "SELECT device_id FROM device_common WHERE located_cabinet = ? AND TYPE='server' and  status=1";
    /**
     * 通过服务器主键获取服务器最新的温度及功率
     */

    String SERVER_TEMP_POWER_ID = "SELECT server_power,inlet_temp,server_sn FROM state_grid_collect_info WHERE server_sn = ? ORDER BY collect_time DESC LIMIT 1";
    /**
     * 获取在使用的机柜
     */
    String USER_CABINET = "SELECT device_id  FROM device_common WHERE type = 'cabinet' and status = 1";

    /**
     * 通过机柜主键查询服务器主键xxx
     */
    String GET_SERVER_IDS = "SELECT device_id  FROM device_common WHERE located_cabinet = ? AND TYPE='server' and status = 1";

    /**
     * 通过服务器主键查询服务器最新温度数据
     */
    String SERVER_TEMP = "SELECT inlet_temp,server_sn  FROM state_grid_collect_info WHERE server_sn = ? ORDER BY collect_time DESC LIMIT 1";

    /**
     * 获取温度最低服务器
     */
    String SERVER_LOWEST_TEMPERATURE="SELECT inlet_temp,server_sn   FROM state_grid_collect_info a \n" +
        "             left join device_common b on b.device_id =a.server_sn  and  type ='server' and b.status = 1\n" +
        "             left join device_common c on c.device_id =b.located_cabinet and c.status = 1 \n" +
        "             ORDER BY a.id desc,a.inlet_temp asc LIMIT 1";

    /**
     * 查询服务器所属机柜
     */
    String SERVER_OWNING_CABINET="select located_cabinet from  device_common where device_id =?  and status = 1 limit 1";
}
